package com.ruoyi.iot.util;

import cn.hutool.core.text.StrJoiner;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.ruoyi.iot.constant.DbDataTypeEnum;
import com.ruoyi.iot.domain.MetaDataDict;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SqlUtils {
    /**
     * 转换器，将值转换成SQL语句使用的值
     *
     * @param object 要转换的值
     * @return 转换后的SQL片段
     */
    public static String convertValue(Object object) {
        String value = "";
        if (object == null) {
            return null;
        }
        value = object.toString();
        if (object instanceof Number) {
            value = "" + object.toString() + "";
        } else if (object instanceof String) {
            value = "'" + object.toString() + "'";
        } else {
            value = "'" + object.toString() + "'";
        }
        return value;
    }


    /**
     * 创建gis数据表create语句
     * @param tablename 表名
     * @param fieldlist 字段列表
     * @return
     */
    public static String buildCreateGisSql(String tablename , List<MetaDataDict> fieldlist){

        List<String> files=new ArrayList<>();
        List<String> comments=new ArrayList<>();

        for (MetaDataDict dict:fieldlist) {
            String pk=dict.isPk()?"PRIMARY KEY":"";
            files.add("\""+dict.getKey()+"\" "+ DbDataTypeEnum.getName(dict.getValueType().getCode())+" "+pk);
            comments.add("comment on column \""+tablename+"\".\""+dict.getKey()+"\" is '"+dict.getComment()+"'");
        }

        String fieldsql= StrUtil.join(",",files);
        String commentsql=StrUtil.join(";",comments);
        StrJoiner joiner=new StrJoiner(" ");
        String createsql="";
        joiner.append("create table ");
        joiner.append(tablename+ " (");
        joiner.append(fieldsql+");");
        joiner.append(commentsql+";");
        createsql=joiner.toString();
        return createsql;
    }

    /**
     * 创建表新增字段语句
     * @param tablename 表名
     * @param fieldlist 字段列表
     * @return
     */
    public static String buildAddColumnSql(String tablename ,List<MetaDataDict> fieldlist){

        List<String> files=new ArrayList<>();
        List<String> comments=new ArrayList<>();

        for (MetaDataDict dict:fieldlist) {
            files.add("ALTER TABLE "+tablename+" ADD column \""+dict.getKey()+"\" "+ DbDataTypeEnum.getName(dict.getValueType().getCode()));
            comments.add("comment on column "+tablename+".\""+dict.getKey()+"\" is '"+dict.getComment()+"'");
        }

        String fieldsql= StrUtil.join(";",files);
        String commentsql=StrUtil.join(";",comments);
        StrJoiner joiner=new StrJoiner(" ");
        String createsql="";
        joiner.append(fieldsql+";");
        joiner.append(commentsql+";");
        createsql=joiner.toString();
        return createsql;
    }

    /**
     * 创建表删除增字段语句
     * @param tablename 表名
     * @param fieldlist 字段列表
     * @return
     */
    public static String buildDropColumnSql(String tablename ,List<MetaDataDict> fieldlist){

        List<String> files=new ArrayList<>();

        for (MetaDataDict dict:fieldlist) {
            files.add("ALTER TABLE \""+tablename+"\" DROP \""+dict.getKey()+"\"");
        }

        String fieldsql= StrUtil.join(";",files);
        StrJoiner joiner=new StrJoiner(" ");
        String createsql="";
        joiner.append(fieldsql+";");
        createsql=joiner.toString();
        return createsql;
    }

    /**
     * 创建表修改字段语句
     * @param tablename 表名
     * @param fieldlist 字段列表
     * @return
     */
    public static String buildUpdateColumnSql(String tablename ,List<MetaDataDict> fieldlist){

        List<String> files=new ArrayList<>();
        List<String> comments=new ArrayList<>();

        for (MetaDataDict dict:fieldlist) {
//            files.add("ALTER TABLE "+tablename+" ADD column if not exists \""+dict.getKey()+"\" "+ DbDataTypeEnum.getName(dict.getValueType().getCode()));
            files.add("ALTER TABLE "+tablename+" ALTER \""+dict.getKey()+"\" TYPE "+ DbDataTypeEnum.getName(dict.getValueType().getCode()));
            comments.add("comment on column "+tablename+".\""+dict.getKey()+"\" is '"+dict.getComment()+"'");
        }

        String fieldsql= StrUtil.join(";",files);
        String commentsql=StrUtil.join(";",comments);
        StrJoiner joiner=new StrJoiner(" ");
        String createsql="";
        joiner.append(fieldsql+";");
        joiner.append(commentsql+";");
        createsql=joiner.toString();
        return createsql;
    }

    /**
     * 创建表修改字段注释语句
     * @param tablename 表名
     * @param fieldlist 字段列表
     * @return
     */
    public static String buildUpdateColumnCommentSql(String tablename ,List<MetaDataDict> fieldlist){

        List<String> comments=new ArrayList<>();

        for (MetaDataDict dict:fieldlist) {
            comments.add("comment on column \""+tablename+"\".\""+dict.getKey()+"\" is '"+dict.getComment()+"'");
        }

        String commentsql=StrUtil.join(";",comments);
        StrJoiner joiner=new StrJoiner(" ");
        String createsql="";
        joiner.append(commentsql+";");
        createsql=joiner.toString();
        return createsql;
    }

    /**
     * 创建gis数据表create语句
     * @param tablename 表名
     * @param fieldlist 字段列表
     * @param  tablecomment 表注释
     * @return
     */
    public static String buildCreateGisSql(String tablename ,List<MetaDataDict> fieldlist,String tablecomment){

        List<String> files=new ArrayList<>();
        List<String> comments=new ArrayList<>();

        for (MetaDataDict dict:fieldlist) {
            String pk=dict.isPk()?"PRIMARY KEY":"";
            files.add("\""+dict.getKey()+"\" "+ DbDataTypeEnum.getName(dict.getValueType().getCode())+" "+pk);
            comments.add("comment on column \""+tablename+"\".\""+dict.getKey()+"\" is '"+dict.getComment()+"'");
        }
        if(ObjectUtil.isNotEmpty(tablecomment)){
            comments.add("comment on table \""+tablename+"\" is '"+tablecomment+"'");
        }

        String fieldsql= StrUtil.join(",",files);
        String commentsql=StrUtil.join(";",comments);
        StrJoiner joiner=new StrJoiner(" ");
        String createsql="";
        joiner.append("create table ");
        joiner.append("\""+tablename+ "\" (");
        joiner.append(fieldsql+");");
        joiner.append(commentsql+";");
        createsql=joiner.toString();
        return createsql;
    }

    /**
     * 构建insert语句
     * @param tablename
     * @param hashMap
     * @return
     */
    public static String buildInsertSql(String tablename, HashMap<String,Object> hashMap){
        String sql="";
        StrJoiner joiner=new StrJoiner(" ");
        List<String> keys=new ArrayList<>();
        List<String> values=new ArrayList<>();
        for (String key:hashMap.keySet()) {
            keys.add("\""+key+"\"");
            String geomStr="geom";
            if(geomStr.equals(key)) {
                values.add((String)hashMap.get(key));
            }else{
                values.add(convertValue(hashMap.get(key)));
            }
        }
        joiner.append("insert into \""+tablename+"\"");
        joiner.append("("+ StrUtil.join(",",keys));
        joiner.append(") values(");
        joiner.append(StrUtil.join(",",values)+")");
        sql=joiner.toString();
        return sql;
    }

    /**
     * 构建批量insert语句
     * @param tablename
     * @param list
     * @return
     */
    public static String buildBatchInsertSql(String tablename, List<HashMap<String, Object>> list,String geom) {

        String sql="";
        List<String> listvalues=new ArrayList<>();
        for(HashMap<String,Object> hashMap:list) {
            StrJoiner joiner=new StrJoiner(" ");
            List<String> keys=new ArrayList<>();
            List<String> values=new ArrayList<>();
            for (String key : hashMap.keySet()) {
                keys.add(key);
                if(ObjectUtil.isNotEmpty(geom)&&key.equals(geom)){
                    values.add((String)hashMap.get(key));
                }else {
                    values.add(convertValue(hashMap.get(key)));
                }
            }
            joiner.append("insert into "+tablename);
            joiner.append("("+ StrUtil.join(",",keys));
            joiner.append(") ");
            joiner.append(" values("+StrUtil.join(",",values)+")");
            String ss=joiner.toString();
            listvalues.add(ss);
        }
        sql=StrUtil.join(";",listvalues);
        return sql;
    }


    /**
     * 构建type查询语句
     * @param tablename
     * @param typename
     * @return
     */
    public static String buildTypeSql(String tablename,String typename){
        String sql="";
        StrJoiner joiner=new StrJoiner(" ");
        joiner.append("select * from ");
        joiner.append(tablename);
        joiner.append(" where type='"+typename+"'");
        sql=joiner.toString();
        return sql;
    }


    /**
     * 生成更新语句
     *
     * @param tableName 表名
     * @param pkName 主键名
     * @param map 要更新的数据
     * @return SQL语句
     */
    public static String buildUpdateSql(String tableName, String pkName, Map<String, Object> map) {
        Object pk = map.get(pkName);
        StringBuilder sql = new StringBuilder();
        sql.append("update " + tableName + " t set ");
        List<String> valueList = new ArrayList<String>();
        for (String key : map.keySet()) {
            // 更新时忽略主键
            if (pkName.equals(key)) {
                continue;
            }
            Object value = map.get(key);
            if (value == null || StrUtil.isBlank(value.toString())) {
                continue;
            }
            valueList.add("\""+key + "\" = " + convertValue(value));
        }
        sql.append(StrUtil.join(",",valueList));
        sql.append(" where " + pkName + " = " + convertValue(pk));
        return sql.toString();
    }

    /**
     * 构建删除SQL语句
     *
     * @param tableName 表名
     * @param pkName 主键名
     * @param pk 主键值
     * @return SQL语句
     */
    public static String buildDeleteSql(String tableName, String pkName, Object pk) {
        String sql = "delete from \"" + tableName + "\" where \"" + pkName + "\" = " + convertValue(pk);
        return sql;
    }
}
